package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.OrgDto;
import com.hualude.roadsign.model.OrgEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * Created by xu-jiahui on 2017/2/25.
 */

public interface OrgRepository extends CrudRepository<OrgEntity, Long> {
    /**
     * @Description: 获取用于DT显示公司信息
     * @Author: xu-jiaHui
     * @Date: 2017/2/25 16:46
     */
    public List<OrgDto> getOrg4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 获取用于DT显示的公司数量
     * @Author: xu-jiaHui
     * @Date: 2017/2/25 17:06
     */
    public Integer getCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 根据Id更新公司信息
     * @Author: xu-jiaHui
     * @Date: 2017/2/26 12:35
     */
    @Modifying
    @Query(value = "update OrgEntity t set t.orgName=?1,t.orgPerson=?2,t.orgPersonMobile=?3,t.orgMobile=?4,t.orgFax=?5,t.orgWeb=?6,t.orgRemark=?7 ,t.orgType=?9,t.orgAddress=?10,t.orgEmail=?11 where t.orgId=?8")
    public Integer updateOrgById(String orgName, String orgPerson, String orgPersonMobile, String orgMobile, String orgFax, String orgWeb, String orgRemark, Integer orgId, Integer orgType, String orgAddress, String orgEmail) throws Exception;

    /**
     * @Description: 停用公司
     * @Author: xu-jiaHui
     * @Date: 2017/2/26 19:42
     */
    @Modifying
    @Query(value = "update OrgEntity t set t.orgState=1 where t.orgId=?1")
    public Integer deactivateOrgById(Integer orgId) throws Exception;

    /**
     * @Description: 启用公司
     * @Author: xu-jiaHui
     * @Date: 2017/4/25 20:12
     */
    @Modifying
    @Query(value = "update OrgEntity t set t.orgState=0 where t.orgId=?1")
    public Integer activateOrgById(Integer orgId) throws Exception;

    /**
     * @Description: 获取养护公司的Id和名字
     * @Author: shao-yikun
     * @Date: 2017/3/5 12:59
     */
    @Query(value = "select new com.hualude.roadsign.dto.OrgDto(orgId, orgName,orgType) from OrgEntity  where orgState=0")
    public List<OrgDto> getOrgNameAndId() throws Exception;

    /**
     * @Description: 获取监管单位
     * @Author: xu-jiaHui
     * @Date: 2017/4/9 19:51
     */

    @Query(value = "select new com.hualude.roadsign.dto.OrgDto(orgId, orgName) from OrgEntity  where orgType=1" +
            "and orgName like CONCAT('%',:strSearch,'%') order by orgId")
    public List<OrgDto> getAllMainOrg(@Param("strSearch") String strSearch) throws Exception;

    @Query(value = "select count(*) from OrgEntity t where t.orgType=1 and t.orgName like CONCAT('%',:strSearch,'%')")
    public Integer getAllMainOrgCount(@Param("strSearch") String strSearch) throws Exception;

    /**
     * @Description: 获得公司类型4Notice
     * @Author: Panjianan
     * @Data: 14:16:2017/4/9
     * @params:
     **/
    @Query(value = "select o.orgType from OrgEntity o where o.orgId = ?1")
    public Integer getOrgType4Notice(Integer orgId) throws Exception;

    /**
     * @Description: 通过公司Id获取父公司Id
     * @Author: xu-jiaHui
     * @Date: 2017/4/9 21:28
     */
    @Query(value = "select o.orgName from OrgEntity o,OrgFatherAndChildEntity fc where o.orgId=fc.mainOrgId and fc.orgId=?1")
    public List<String> getOrgFatherNameByOrgId(Integer orgId) throws Exception;

    /**
     * @Description: 获取主管单位的Id
     * @Author: shao-yikun
     * @Date: 2017/3/5 12:59
     */
    @Query(value = "select new com.hualude.roadsign.dto.OrgDto(fc.mainOrgId) " +
            "from OrgEntity og, OrgFatherAndChildEntity fc " +
            "where fc.orgId = og.orgId and og.orgId = ?1")
    public List<OrgDto> getMainOrgId(Integer orgId) throws Exception;

    /**
     * @Description: 根据公司Id获得名字
     * @Author: shao-yikun
     * @Date: 2017/4/8 21:41
     */
    @Query(value = "select orgName from OrgEntity where orgId = ?1")
    public String getMainOrgName(Integer orgId);

    /**
     * @Description: 获取指定OrgId的候选主管单位
     * @Author: xu-jiaHui
     * @Date: 2017/4/11 21:23
     */
    public List<OrgDto> getCandidateMainOrgIdAndNameByOrgId(DTRequestDto dtRequestDto, Integer orgId) throws Exception;

    public Integer getCandidateMainOrgCountByOrgId(DTRequestDto dtRequestDto, Integer orgId) throws Exception;

    /**
     * @Description: 获取名字相同的公司的个数
     * @Author: xu-jiaHui
     * @Date: 2017/5/1 0:06
     */
    @Query(value = "select count(*) from OrgEntity where orgName=?1")
    public Integer getCountByOrgName(String orgName) throws Exception;

    /**
     * @Description: 删除公司
     * @Author: xu-jiaHui
     * @Date: 2017/5/1 23:18
     */
    @Modifying
    @Query(value = "delete from OrgEntity where orgId=?1")
    public Integer deleteOrgByOrgId(Integer orgId) throws Exception;

    /**
     * @Description: 获取公司用于选择
     * @Author: xu-jiaHui
     * @Date: 2017/5/6 20:57
     */
    public List<OrgDto> getOrg4Select(Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 添加页面
     * @Author: shao-yikun
     * @Date: 2017/7/23 下午3:35
     */
    @Modifying
    @Query(value = "update OrgEntity set orgMenu = ?2 where orgId = ?1")
    public Integer addMenu(Integer orgId, String orgMenu) throws Exception;

    /**
     * @Description: 获取页面
     * @Author: shao-yikun
     * @Date: 2017/7/23 下午3:40
     */
    @Query(value = "select orgMenu from OrgEntity where orgId = ?1")
    public String getOrgMenu(Integer orgId) throws Exception;
}
